package models;

import java.util.*;

import javax.persistence.*;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.Expr;
import com.avaje.ebean.SqlQuery;
import com.avaje.ebean.SqlRow;

import play.db.ebean.*;
import play.data.format.*;
import play.data.validation.*;


/**
 * User entity managed by Ebean
 */
@Entity
@Table(name="examination_profiles")
public class ExaminationProfiles extends Model {

	public static final long serialVersionUID = 1L;

	@Id
	@Column(name="idexamination_profiles")
	public long id;

    @Column(name="profile_name")
    public String profileName;

    @OneToOne
    @JoinColumn(name="examination_id")
    public Examination examination;
    
    @Column(name="updated_on")
    public Date updatedOn;

    @OneToOne
    @JoinColumn(name="updated_by")
    public User updatedBy;

    // -- Queries

    public static Model.Finder<String,ExaminationProfiles> find = new Model.Finder(String.class, ExaminationProfiles.class);

    public static List<ExaminationProfiles> getProfiles(Long userId) {
        return find
        	.join("examination")
        	.where()
        	.add(Expr.raw("updated_by is null or updated_by = "+userId))
        	.orderBy("profile_name")
            .findList();
    }

    public static List<String> getDistinctProfiles (Long userId) {
    	String sql = "select distinct profile_name from examination_profiles where updated_by is null or updated_by = "+userId;
    	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    	List<SqlRow> rows = sqlQuery.findList();
    	List<String> names= new ArrayList<String>(rows.size());
        for (SqlRow row : rows){
        	names.add(row.getString("profile_name"));
        }
        
        return names;
    }

    public static List<ExaminationProfiles> getProfilesForDoc(Long userId, String profile) {
        return find
        	.join("examination")
        	.where()
        	.eq("updated_by", userId)
        	.like("profile_name", profile+"%")
        	.findList();
    }
    
    public static List<ExaminationProfiles> getProfilesForDocSearch(Long userId, String profile) {
        return find
        	.join("examination")
        	.where()
        	.eq("updated_by", userId)
        	.eq("profile_name", profile)
        	.findList();
    }
    public static List<Long> getExamsForDoc(Long userId, String profile) {
    	String sql = "select distinct examination_id from examination_profiles where updated_by =:ud and profile_name =:pn";
    	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    	sqlQuery.setParameter("ud", userId);
    	sqlQuery.setParameter("pn", profile);
    	List<SqlRow> rows = sqlQuery.findList();
    	List<Long> names= new ArrayList<Long>(rows.size());
        for (SqlRow row : rows){
        	names.add(row.getLong("examination_id"));
        }
        
        return names;
    }

}

